//! @Author: DengLibin 榆霖
//! @Date:  2022-06-30 16:18:33
//! @Description: 




use std::collections::HashMap;

use common::date;
use mysql_async::{prelude::{WithParams, BatchQuery, Query, Queryable}, params, TxOpts};

use crate::{entity::user::User, pojo::PageInfo};



/// @Author: DengLibin 榆霖
/// @Date:  2022-06-30 16:59:54
/// @Description: 添加
pub async fn insert(u: &User, pool: &mysql_async::Pool)->Result<(), Box<dyn std::error::Error>>{
    let mut conn = pool.get_conn().await?;

    let users = vec![u];
    r"INSERT INTO user (id,deleted,create_time,
        update_time,create_by,update_by,
        user_login_name, name, phone_num, 
        passwords, image_url)
      VALUES (:id, :deleted, :create_time,
        :update_time, :create_by, :update_by,
        :user_login_name, :name, :phone_num, 
        :passwords, :image_url)".with(
            users.iter().map(|u| params! {
                "id" => u.id,
                "deleted" => u.deleted,
                "create_time" => date::get_chrono_local_now_time_str(),
                "update_time" => date::get_chrono_local_now_time_str(),
                "create_by" => u.create_by,
                "update_by" => u.update_by,
                "user_login_name" => u.user_login_name.as_str(),
                "name" => u.name.as_str(),
                "phone_num" => u.phone_num.as_str(),
                "passwords" => u.passwords.as_str(),
                "image_url" => {
                    match &u.image_url {
                        Some(url) => url.as_str(),
                        None=> "",
                    }
                },
            })
        ).batch(&mut conn).await?;
       
    // Dropped connection will go to the pool
    drop(conn);

    // The Pool must be disconnected explicitly because
    // it's an asynchronous operation.
    //pool.disconnect().await?;
    Ok(())
   
}
pub async fn insert_batch(users: &Vec<User>, pool: &mysql_async::Pool)->Result<(), Box<dyn std::error::Error>>{
    let mut conn = pool.get_conn().await?;

    r"INSERT INTO user (id,deleted,create_time,
        update_time,create_by,update_by,
        user_login_name, name, phone_num, 
        passwords, image_url)
      VALUES (:id, :deleted, :create_time,
        :update_time, :create_by, :update_by,
        :user_login_name, :name, :phone_num, 
        :passwords, :image_url)".with(
            users.iter().map(|u| params! {
                "id" => u.id,
                "deleted" => u.deleted,
                "create_time" => date::get_chrono_local_now_time_str(),
                "update_time" => date::get_chrono_local_now_time_str(),
                "create_by" => u.create_by,
                "update_by" => u.update_by,
                "user_login_name" => u.user_login_name.as_str(),
                "name" => u.name.as_str(),
                "phone_num" => u.phone_num.as_str(),
                "passwords" => u.passwords.as_str(),
                "image_url" => {
                    match &u.image_url {
                        Some(url) => url.as_str(),
                        None=> "",
                    }
                },
            })
        ).batch(&mut conn).await?;
       
    // Dropped connection will go to the pool
    drop(conn);

    // The Pool must be disconnected explicitly because
    // it's an asynchronous operation.
    //pool.disconnect().await?;
    Ok(())
   
}

/// @Author: DengLibin 榆霖
/// @Date:  2022-07-11 15:30:21
/// @Description: 查询所有
pub async fn get_all( pool: &mysql_async::Pool) -> Result<Vec<User>, Box<dyn std::error::Error>> {
    let mut conn = pool.get_conn().await?;

    let users = "SELECT id,deleted,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s'),
    DATE_FORMAT(update_time,'%Y-%m-%d %H:%i:%s'),create_by,update_by,
    user_login_name, name, phone_num, 
    passwords, image_url FROM user"
    .with(())
    .map(&mut conn, |(id,deleted,create_time,
        update_time,create_by,update_by,
        user_login_name, name, phone_num, 
        passwords, image_url)| 
            User { id,
                deleted,
                create_time: create_time,
                update_time: update_time,
                create_by,
                update_by,
                user_login_name,
                name, 
                phone_num, 
                passwords, 
                image_url
             }
    ).await?;


    drop(conn);
    return Ok(users);
}


/// @Author: DengLibin 榆霖
/// @Date:  2022-07-11 15:31:17
/// @Description: 分页
pub async fn get_page(page: i32, size: i32, pool: &mysql_async::Pool) -> Result<PageInfo<User>, Box<dyn std::error::Error>>{
    let mut conn = pool.get_conn().await?;
    let sql_list = "SELECT id,deleted,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s'),
    DATE_FORMAT(update_time,'%Y-%m-%d %H:%i:%s'),create_by,update_by,
    user_login_name, name, phone_num, 
    passwords, image_url FROM user";

    //查询总数
    let sql_total = format!("SELECT COUNT(1) FROM ({}) t", sql_list);
    //分页
    let sql_page = format!("{} LIMIT {} OFFSET  {}", sql_list, size, (page -1) * size);

    //列表
    let users = sql_page
    .with(())
    .map(&mut conn, |(id,deleted,create_time,
        update_time,create_by,update_by,
        user_login_name, name, phone_num, 
        passwords, image_url)| {
            User { id,deleted,create_time,
                update_time,create_by,update_by,
                user_login_name, name, phone_num, 
                passwords, image_url }
    }).await?;
    //总数
    let total_option: Option<i32>  = conn.query_first(sql_total).await?;
    drop(conn);
    let total = match total_option {
        Some(i) => i,
        None => 0
    };
    

    return Ok(PageInfo{page: page, size: size, total, list: users});
    
}

/// @Author: DengLibin 榆霖
/// @Date:  2022-07-05 10:06:56
/// @Description: 删除
pub async fn delete(id: i64, pool: &mysql_async::Pool) -> Result<bool, Box<dyn std::error::Error>>{
    let delete_sql = "DELETE FROM user where id = ?";

    let mut conn = pool.get_conn().await?;
    let mut tx = conn.start_transaction(TxOpts::default()).await?;
    tx.exec_drop(delete_sql, 
          (id,)).await?;
    tx.commit().await?;
    return Ok(true)
}
/// @Author: DengLibin 榆霖
/// @Date:  2022-07-05 10:17:46
/// @Description: 更新
pub async fn update(map: HashMap<&str, &str>, id: i64, pool: &mysql_async::Pool)-> Result<bool, Box<dyn std::error::Error>> {
    let mut update_sql = String::from("UPDATE user SET ");
    let size = map.len();
    let mut index = 0;
    //保存参数
    let mut values: Vec<&str>= vec![];
      //遍历
      for (k, v) in map {
        update_sql.push_str(k);
        update_sql.push_str("=");
        update_sql.push_str("?");
        //参数
        values.insert(index, v);
        index += 1;
        if index < size {
            update_sql.push(',');
        }
    }
    update_sql.push_str(" WHERE ID = ?");
    let str_id = format!("{}", id);
    //id
    values.insert(index, &str_id);

    let mut conn = pool.get_conn().await?;

    let mut tx = conn.start_transaction(TxOpts::default()).await?;
    //执行更新
    tx.exec_drop(update_sql.as_str(), 
          values).await?;
    tx.commit().await?;
    return Ok(true);
}